﻿using System;
using System.Data;
using System.Threading.Tasks;
using Dapper;
using AyuBookmark.Common.Interfaces;
using AyuBookmark.Common.Models;
using Microsoft.Data.Sqlite;

namespace AyuBookmark.Common.Repositories
{
    /// <summary>
    /// 用户仓储实现
    /// </summary>
    public class UserRepository : IUserRepository
    {
        private readonly string _connectionString;
        private const string TableName = "Users";

        public UserRepository(string connectionString)
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
        }

        /// <inheritdoc />
        public async Task<User> GetByIdAsync(int id)
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryFirstOrDefaultAsync<User>(
                $"SELECT * FROM {TableName} WHERE Id = @id",
                new { id });
        }

        /// <inheritdoc />
        public async Task AddAsync(User user)
        {
            using var connection = await GetConnectionAsync();
            await connection.ExecuteAsync(
                $@"INSERT INTO {TableName} 
                   (Username, Email, PasswordHash, CreatedTime, LastLoginTime)
                   VALUES (@Username, @Email, @PasswordHash, @CreatedTime, @LastLoginTime)",
                user);
        }

        /// <inheritdoc />
        public async Task UpdateAsync(User user)
        {
            using var connection = await GetConnectionAsync();
            await connection.ExecuteAsync(
                $@"UPDATE {TableName} SET 
                   Username = @Username, 
                   Email = @Email,
                   PasswordHash = @PasswordHash,
                   LastLoginTime = @LastLoginTime
                   WHERE Id = @Id",
                user);
        }

        /// <summary>
        /// 根据用户名获取用户
        /// </summary>
        public async Task<User> GetByUsernameAsync(string username)
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryFirstOrDefaultAsync<User>(
                $"SELECT * FROM {TableName} WHERE Username = @username",
                new { username });
        }

        /// <summary>
        /// 根据邮箱获取用户
        /// </summary>
        public async Task<User> GetByEmailAsync(string email)
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryFirstOrDefaultAsync<User>(
                $"SELECT * FROM {TableName} WHERE Email = @email",
                new { email });
        }

        /// <summary>
        /// 更新用户最后登录时间
        /// </summary>
        public async Task UpdateLastLoginTimeAsync(int userId)
        {
            using var connection = await GetConnectionAsync();
            await connection.ExecuteAsync(
                $"UPDATE {TableName} SET LastLoginTime = @now WHERE Id = @userId",
                new { userId, now = DateTime.Now });
        }

        #region 辅助方法
        private async Task<IDbConnection> GetConnectionAsync()
        {
            var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();
            return connection;
        }
        #endregion
    }
}